package com.cold.manage.service.impl;

import com.cold.common.domain.AlertMessage;
import com.cold.common.utils.StringUtils;
import com.cold.manage.service.IDruidService;
import com.cold.manage.util.DruidHelper;
import org.springframework.stereotype.Service;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
public class DruidServiceImpl implements IDruidService {

    private SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    @Override
    public List<AlertMessage> selectMessages(Map<String, Object> params) {
        DruidHelper helper = new DruidHelper();
        Connection connection = helper.getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(querySQL(params));
            List<AlertMessage> messages = new ArrayList<>();
            while(resultSet.next()){
                AlertMessage message = readMessage(resultSet);
                messages.add(message);
            }
            return messages;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            helper.close(connection,statement,resultSet);
        }
    }

    @Override
    public Map<String, Object> selectChartTemHum(Map<String, Object> params) {
        List<AlertMessage> messages = selectMessages(params);
        List<String> times = new ArrayList<>();
        List<Long> tems = new ArrayList<>();
        List<Long> hums = new ArrayList<>();
        //给数组赋值
        for (AlertMessage message : messages) {
            String strTime = format.format(message.getSendTime());
            times.add(strTime.substring(11));
            tems.add(message.getTem());
            hums.add(message.getHum());
        }
        //按时间重新排序
        Collections.reverse(times);
        Collections.reverse(tems);
        Collections.reverse(hums);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("times", times);
        map.put("tems", tems);
        map.put("hums", hums);
        return map;
    }

    @Override
    public List<String> selectHostNames() {
        String sql = "select hostName from cold_chain_data_druid group by hostName";
        DruidHelper helper = new DruidHelper();
        Connection connection = helper.getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            List<String> messages = new ArrayList<>();
            while(resultSet.next()){
                messages.add(resultSet.getString("hostName"));
            }
            return messages;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            helper.close(connection,statement,resultSet);
        }
    }

    @Override
    public List<AlertMessage> selectLatestMessages() {
        //获得所有主机名
        List<String> hosts = selectHostNames();
        List<AlertMessage> latestMsgs = new ArrayList<>();
        for(String host : hosts){
            //找到每个主机的最近一条消息
            AlertMessage message = selectHostLatestMessages(host);
            latestMsgs.add(message);
        }
        return latestMsgs;
    }

    @Override
    public AlertMessage selectHostLatestMessages(String hostName) {
        String sql = "select * from cold_chain_data_druid where hostName = '"+ hostName +"' order by __time desc limit 1";
        DruidHelper helper = new DruidHelper();
        Connection connection = helper.getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            if(resultSet.next()){
                return readMessage(resultSet);
            }
            return null;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            helper.close(connection,statement,resultSet);
        }
    }


    private AlertMessage readMessage(ResultSet rs) throws Exception {
        AlertMessage messageEntity = new AlertMessage();
        messageEntity.setDeviceCode(rs.getString("deviceCode"));
        messageEntity.setHostName(rs.getString("hostName"));
        messageEntity.setCompanyName(rs.getString("companyName"));
        messageEntity.setTem(Long.valueOf(rs.getString("tem")));
        messageEntity.setMaxTem(Long.valueOf(rs.getString("maxTem")));
        messageEntity.setMinTem(Long.valueOf(rs.getString("minTem")));
        messageEntity.setHum(Long.valueOf(rs.getString("hum")));
        messageEntity.setMaxHum(Long.valueOf(rs.getString("maxHum")));
        messageEntity.setMinHum(Long.valueOf(rs.getString("minHum")));
        messageEntity.setAlertTem(Long.valueOf(rs.getString("alertTem")));
        messageEntity.setAlertHum(Long.valueOf(rs.getString("alertHum")));
        messageEntity.setDeviceStatus(rs.getString("deviceStatus"));
        messageEntity.setLat(rs.getString("lat"));
        messageEntity.setLon(rs.getString("lon"));
        messageEntity.setSendTime(format.parse(rs.getString("sendTime")));
        return messageEntity;
    }

    /**
     * 拼接SQL
     * @param params
     * @return
     */
    private String querySQL(Map<String, Object> params) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT * from cold_chain_data_druid where 1= 1 ");
        //设备编码
        String hostName = (String) params.get("hostName");
        if (!StringUtils.isEmpty(hostName)) {
            stringBuffer.append(" and hostName = '");
            stringBuffer.append(hostName);
            stringBuffer.append("' ");
        }
        //前台传入开始、结束时间
        String startTime = (String) params.get("startTime");
        if (!StringUtils.isEmpty(startTime)) {
            stringBuffer.append(" and __time > '");
            stringBuffer.append(startTime);
            stringBuffer.append("' ");
        }
        String endTime = (String) params.get("endTime");
        if (!StringUtils.isEmpty(endTime)) {
            stringBuffer.append(" and __time < '");
            stringBuffer.append(endTime);
            stringBuffer.append("' ");
        }
        stringBuffer.append(" order by __time desc");
        //查询条数
        String limit = params.get("limit") != null ? params.get("limit").toString().trim() : "10";
        stringBuffer.append(" limit ");
        stringBuffer.append(limit);
        System.out.println(stringBuffer.toString());
        return stringBuffer.toString();
    }
}
